# 导入库
from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)


# 配置信息
class DbConfig(object):
    # 设置连接数据库的url
    SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:12345@localhost:3306/flask_test"
    # 设置每次请求结束后会自动提交数据库中的改动
    SQLALCHEMY_COMMIT_ON_TEARDOWN = True
    # 可修改
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    # 查询时会显示原始SQL语句
    # SQLALCHEMY_ECHO = True


# 初始化配置
app.config.from_object(DbConfig)
# 获取SQLAlchemy对象
db = SQLAlchemy(app)


class PhoneType(db.Model):
    # 定义表名
    __tablename__ = "tbl_phonetype"
    # 类型ID、类型名
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(64), unique=True)
    # 关联
    phone = db.relationship("Phone", backref="phonetype")

    def __str__(self):
        return "PhoneType:%s" % self.name


class Phone(db.Model):
    # 定义表名
    __tablename__ = "tbl_phone"
    # 手机ID、品牌名、手机型号、价格
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    brand = db.Column(db.String(64), unique=True)
    name = db.Column(db.String(64), unique=True, index=True)
    price = db.Column(db.Float)
    # 外键约束
    type_id = db.Column(db.Integer, db.ForeignKey("tbl_phonetype.id"))

    # 型号
    def __repr__(self):
        return "手机型号:%s" % self.name


if __name__ == '__main__':
    # 获取所有
    # all_data = Phone.query.all()
    # print("所有数据:",all_data)
    # print("-"*30)
    # first_data = Phone.query.first()
    # print("第1条数据:",first_data)
    # app.run(debug=True)
    # app.run(debug=True)

    # phone = Phone.query.filter_by(brand="Apple").all()
    # print("品牌为Apple的数据:",phone)
    # print("-" * 30)
    # phone_list = Phone.query.filter(Phone.brand.endswith("I")).all()
    # print("结尾为I的所有数据:",phone_list)
    # phone1_list = Phone.query.filter(Phone.name=="Mi 10").all()
    # print("型号为Mi 10的数据:", phone1_list)
    # print("-" * 30)
    # phone2_list = Phone.query.filter(Phone.name != "Mate 40").all()
    # print("型号不为Mate 40的所有数据:",phone2_list)
    # from sqlalchemy import and_, not_, or_
    #
    # # 品牌为HUAWEI,且价格为8999
    # phone_list1 = Phone.query.filter(and_(Phone.brand == "HUAWEI", Phone.price == 8999)).all()
    # print("品牌为HUAWEI,且价格为8999的数据:", phone_list1)
    # print("-" * 30)
    # # 品牌为HUAWEI, 或者价格为8999
    # phone_list2 = Phone.query.filter(or_(Phone.brand == "HUAWEI", Phone.price == 8999)).all()
    # print("品牌为HUAWEI,或者价格为8999的数据:", phone_list2)
    # print("-" * 30)
    # # 型号不是Mi 10
    # phone_list3 = Phone.query.filter(not_(Phone.name == "Mi 10")).all()
    # print("型号不是Mi 10的数据:", phone_list2)

    # phone = Phone.query.filter(Phone.brand=="黑鲨").all()
    # print("手机:",phone[0].brand)
    # print("-" * 30)
    # db.session.delete(phone[0])
    # db.session.commit()
    # all_phone = Phone.query.all()
    # print("所有手机:",all_phone)
    # 查询第1条数据
    # phone = Phone.query.first()
    # print("第1条数据:",phone)
    # print("-" * 30)
    # # 修改品牌
    # phone.name = "Mate 40 Pro 5G"
    # db.session.commit()
    # phone2 = Phone.query.first()
    # print("修改数据后,第1条数据:", phone2)
    #
    # Phone.query.filter_by(brand="MI").update({"name":"XiaoMi"})
    phone_type = PhoneType.query.get(1)
    print("关联手机数据:",phone_type.phone)
    print("----------------------------------")
    phone = Phone.query.get(5)
    print("类型数据:",phone.type_id)
    print("类型数据:",phone.phonetype)

